﻿using Microsoft.VisualStudio.Modeling;
using System;
using System.Collections.Generic;
using System.Text;

namespace Project.Co_DataTransformationPrototype
{
    public class MergeMapping : MappingBase
    {

        public MergeMapping(EventContext eventContext)
        {
            this.eventContext = eventContext;
            //generateSQLCodeMapping();
        }

        internal override void generateSQLCodeMapping(StringBuilder existingScript = null)
        {
            strSQLMapping = new StringBuilder();
            
            if (existingScript != null)
                strSQLMapping.Append(existingScript);

            setMergeSQLCode();
        }

        private void setMergeSQLCode()
        {
            object sourceEntity;
            object mergeExpression;
            Entity targetEntity;
            EntityAttribute attribute;
            IList<string> idAttributes;

            attribute = eventContext.elementEventArgs.ModelElement as EntityAttribute;
            
            targetEntity = attribute.Entity;
            if (targetEntity == null)
                targetEntity = attribute.ParentEntity;
            
            eventContext.elementEventArgs.TransactionContext.ContextInfo.TryGetValue(Operators.GetMergeOpTransactionKey, out sourceEntity);
            
            if (sourceEntity != null)
            {
                // temp
                string strTargetName = targetEntity.CurrentName;
                string strSourceName = (sourceEntity as Entity).CurrentName;

                idAttributes = MappingJoinCondition.findMatchingIDAttributes((sourceEntity as Entity), targetEntity);

                // Add attribute target table
                strSQLMapping.AppendFormat(@"/*** --- Merging column {0} from source table {1} to target table {2} --- ***/" + Environment.NewLine, attribute.CurrentName, (sourceEntity as Entity).CurrentName, targetEntity.CurrentName);
                strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1} ADD {2} {3}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, strTargetName, attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(attribute.Type));
                strSQLMapping.AppendLine("GO" + Environment.NewLine);

                // Move column data from source to target table
                if (idAttributes.Count == 2)
                {
                    strSQLMapping.AppendFormat(@"UPDATE [{0}].{1} SET {2} = " + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, strTargetName, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"    (SELECT {0} FROM {1}" + Environment.NewLine, attribute.CurrentName, strSourceName);
                    strSQLMapping.AppendFormat(@"    WHERE {0} = [{1}].{2}" + Environment.NewLine, idAttributes[0], strTargetName, idAttributes[1]);
                    strSQLMapping.AppendLine("    )");
                }
                else
                {
                    strSQLMapping.AppendFormat(@"INSERT INTO [{0}].{1} ({2})" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, strTargetName, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"    SELECT {0}" + Environment.NewLine, attribute.CurrentName);
                    strSQLMapping.AppendFormat(@"    FROM [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, strSourceName);
                }
                strSQLMapping.AppendLine("GO" + Environment.NewLine);

                // Removal of column from source table will be handled by another event
            }
            else if (eventContext.elementEventArgs.TransactionContext.ContextInfo.TryGetValue(Operators.GetMergeOpAttributeTransactionKey, out mergeExpression))
            {
                string strExpression = (string)mergeExpression;

                // Add attribute target table
                strSQLMapping.AppendFormat(@"/*** --- Merging columns in table {0} --- ***/" + Environment.NewLine, targetEntity.CurrentName);
                strSQLMapping.AppendFormat(@"ALTER TABLE [{0}].{1} ADD {2} {3}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, targetEntity.CurrentName, attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(attribute.Type));
                strSQLMapping.AppendLine("GO" + Environment.NewLine);

                // Migrate its data to the target table    
                strSQLMapping.AppendFormat(@"UPDATE [{0}].{1} SET {2} = ({3});" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, targetEntity.CurrentName, attribute.CurrentName, strExpression);
                strSQLMapping.AppendLine("GO" + Environment.NewLine);

                // Removal of original columns with be handled by another event
            }
            else
            {
                throw new ModelingException("Merge Operation: target entity or attribute merge expression has not been defined yet.");
            }
        }
    }
}
